﻿//************************************************************************************************************
//文件名称(File Name)：ProductAllAnalyseMana.cs
//功能描述(Description)：产品分析
//数据表(Tables)：
//作者(Author)： jiangyong
//日期(Create Date)：20130301
//参考文档(Reference)(可选)：
//引用(Using) (可选)﹕
//************************************************************************************************************


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;

namespace AnalyseCrmMana
{
    public class ProductAllAnalyseMana
    {

        string begindate = "";
        string enddate = "";

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="author"></param>
        /// <returns></returns>
        public DataSet GetPlaceTypeAnalyse(string strtype)
        {
            if (strtype == "0")
            {
                begindate = DateTime.Now.AddMonths(-1).ToShortDateString();
                enddate = DateTime.Now.ToShortDateString();
            }
            else
            {
                begindate = DateTime.Now.AddMonths(-6).ToShortDateString();
                enddate = DateTime.Now.ToShortDateString();
            }
            string sql = @"  select  JYPlace , JYTYPE , SUM(jycount) from Tbt_JYLStatis
             where JYDate between '" + begindate + "' and '" + enddate + "'  group by JYPlace,JYTYPE ";
            ExecuteSql sqldbAccess = new ExecuteSql();
            return sqldbAccess.ExecuteDataset(sql);
        }


        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="author"></param>
        /// <returns></returns>
        public DataSet GetCurrencyTypeAnalyse(string strtype)
        {
            if (strtype == "0")
            {
                begindate = DateTime.Now.AddMonths(-1).ToShortDateString();
                enddate = DateTime.Now.ToShortDateString();
            }
            else
            {
                begindate = DateTime.Now.AddMonths(-6).ToShortDateString();
                enddate = DateTime.Now.ToShortDateString();
            }
            string sql = @"  select  JYCurrency , JYTYPE , SUM(jycount) from Tbt_JYLStatis
             where JYDate between '" + begindate + "' and '" + enddate + "'  group by JYCurrency,JYTYPE ";
            ExecuteSql sqldbAccess = new ExecuteSql();
            return sqldbAccess.ExecuteDataset(sql);
        }

        /// <summary>
        /// 佣金统计
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet GetYJStatis(string strtype)
        {
            if (strtype == "0")
            {
                begindate = DateTime.Now.AddMonths(-1).ToShortDateString();
                enddate = DateTime.Now.ToShortDateString();
            }
            else
            {
                begindate = DateTime.Now.AddMonths(-6).ToShortDateString();
                enddate = DateTime.Now.ToShortDateString();
            }
            string sql = @"  select  JYPlace , JYTYPE , SUM(JYMoney) JYMoney  from Tbt_YJStatis
            where JYDate between '" + begindate + "' and '" + enddate + "'  group by JYPlace,JYTYPE ";
            ExecuteSql sqldbAccess = new ExecuteSql();
            return sqldbAccess.ExecuteDataset(sql);
        }



        /// <summary>
        /// 交易人数统计
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet GetYJPeopleStatis(string strtype)
        {
            if (strtype == "0")
            {
                begindate = DateTime.Now.AddMonths(-1).ToShortDateString();
                enddate = DateTime.Now.ToShortDateString();
            }
            else
            {
                begindate = DateTime.Now.AddMonths(-6).ToShortDateString();
                enddate = DateTime.Now.ToShortDateString();
            }
            string sql = @"  select  JYPlace , JYTYPE , SUM(JYPeopleNum) JYPeopleNum  from  Tbt_JYPeople
            where JYDate between '" + begindate + "' and '" + enddate + "'  group by JYPlace,JYTYPE ";
            ExecuteSql sqldbAccess = new ExecuteSql();
            return sqldbAccess.ExecuteDataset(sql);
        }



        /// <summary>
        /// 交易量统计-交易量统计-交易人数统计
        /// add by jiangyong
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet GetTop5JYLCount(string strtype)
        {
            string strType2 = "Plate";
            if (strtype == "0")
            {
                strType2 = "Plate";

            }
            else if (strtype == "1")
            {
                strType2 = "Region";
            }
            else
            {
                strType2 = "Style";
            }
            begindate = DateTime.Now.AddMonths(-3).ToShortDateString();
            enddate = DateTime.Now.ToShortDateString();

            string sql = @"  select top 5 " + strType2 + ", SUM(JYNum) JYNum  from   Tbt_PlateJYStatis "
           + " where JYDate between '" + begindate + "' and '" + enddate + "'  group by " + strType2;
            ExecuteSql sqldbAccess = new ExecuteSql();
            return sqldbAccess.ExecuteDataset(sql);
        }

        /// <summary>
        /// 交易金额统计
        /// add by jiangyong
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet GetTop5JYMoneyCount(string strtype)
        {
            string strType2 = "Plate";
            if (strtype == "0")
            {
                strType2 = "Plate";

            }
            else if (strtype == "1")
            {
                strType2 = "Region";
            }
            else
            {
                strType2 = "Style";
            }
            begindate = DateTime.Now.AddMonths(-3).ToShortDateString();
            enddate = DateTime.Now.ToShortDateString();

            string sql = @"  select top 5 " + strType2 + ", SUM(JYMoney) JYNum  from   Tbt_PlateJYStatis "
           + " where JYDate between '" + begindate + "' and '" + enddate + "'  group by " + strType2;
            ExecuteSql sqldbAccess = new ExecuteSql();
            return sqldbAccess.ExecuteDataset(sql);
        }

        /// <summary>
        /// 交易人数统计
        /// add by jiangyong
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet GetTop5JYPeopleCount(string strtype)
        {
            string strType2 = "Plate";
            if (strtype == "0")
            {
                strType2 = "Plate";

            }
            else if (strtype == "1")
            {
                strType2 = "Region";
            }
            else
            {
                strType2 = "Style";
            }
            begindate = DateTime.Now.AddMonths(-3).ToShortDateString();
            enddate = DateTime.Now.ToShortDateString();

            string sql = @"  select top 5 " + strType2 + ", SUM(JYPeopleNum) JYNum  from   Tbt_PlateJYStatis "
           + " where JYDate between '" + begindate + "' and '" + enddate + "'  group by " + strType2;
            ExecuteSql sqldbAccess = new ExecuteSql();
            return sqldbAccess.ExecuteDataset(sql);
        }


        /// <summary>
        /// 佣金额比例统计
        /// add by jiangyong
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet GetTop5YJMoneyPecent(string strtype)
        {
            string strType2 = "Plate";
            if (strtype == "0")
            {
                strType2 = "Plate";

            }
            else if (strtype == "1")
            {
                strType2 = "Region";
            }
            else
            {
                strType2 = "Style";
            }
            begindate = DateTime.Now.AddMonths(-3).ToShortDateString();
            enddate = DateTime.Now.ToShortDateString();

            string sql = @"  select top 5 " + strType2 + ", SUM(YJMoney) YJMoney,sum(YJPecent) as YJPecent from   Tbt_PlateJYStatis "
           + " where JYDate between '" + begindate + "' and '" + enddate + "'  group by " + strType2;
            ExecuteSql sqldbAccess = new ExecuteSql();

            DataColumn col1 = new DataColumn();
            col1.ColumnName = "Pecent";

            return sqldbAccess.ExecuteDataset(sql);
        }

        /// <summary>
        /// --各类型基金交易次数-交易金额-交易客户数
        /// add by jiangyong
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet GetLatest3MonthJYCount(string strStatisColumn)
        {
            begindate = DateTime.Now.AddMonths(-3).ToShortDateString();
            enddate = DateTime.Now.ToShortDateString();

            string sql = @"  select JYTypeDetail , SUM(" + strStatisColumn + ") JYNum  from   Tbt_PlateJYStatis "
           + " where JYDate between '" + begindate + "' and '" + enddate + "'  group by JYTypeDetail ";
            ExecuteSql sqldbAccess = new ExecuteSql();
            return sqldbAccess.ExecuteDataset(sql);
        }


        /// <summary>
        /// 佣金额-占比
        /// add by jiangyong
        /// </summary>
        /// <param name="begindate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataSet GetLatest3JJYJPecent()
        {
            begindate = DateTime.Now.AddMonths(-3).ToShortDateString();
            enddate = DateTime.Now.ToShortDateString();

            string sql = @"  select JYTypeDetail ,SUM(YJMoney) YJMoney, SUM(YJPecent) YJPecent  from   Tbt_PlateJYStatis "
           + " where JYType='基金' and JYDate between '" + begindate + "' and '" + enddate + "'  group by JYTypeDetail ";
            ExecuteSql sqldbAccess = new ExecuteSql();
            return sqldbAccess.ExecuteDataset(sql);
        }
    }
}
